﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using System.Data.SqlClient;
using System.Data;
using System.Collections;

using XJX.DBCore;

namespace XJX.DataLayer
{
    public sealed class Book : XJX.DBCore.DBObject
    {
        public Book(string newConnectionString) : base(newConnectionString) { }


        public bool Add_New_Book
        (
            string bname,
            string author,
            string press,
            decimal price,
            string isbn,
            string pym,
            string cname,
            string descript,
            string pname,
            string uid,
            out int ReturnValue,          //返回参数值
            out string ReturnFieldValue   //返回字段值
            )
        { 
            int rowsAffected;//受影响的行数．

            SqlParameter[]
                parameters = {
								 new SqlParameter("@bname",SqlDbType.VarChar,100), 
								 new SqlParameter("@author",SqlDbType.VarChar,100),
								 new SqlParameter("@press",SqlDbType.VarChar,100),
								 new SqlParameter("@price",SqlDbType.Decimal),
								 new SqlParameter("@isbn",SqlDbType.VarChar,13 ),
								 new SqlParameter("@pym",SqlDbType.VarChar,70 ),
								 new SqlParameter("@cname",SqlDbType.VarChar,1000),
								 new SqlParameter("@descript",SqlDbType.VarChar,4000 ),
								 new SqlParameter("@pname",SqlDbType.VarChar,500 ),
								 new SqlParameter("@uid",SqlDbType.UniqueIdentifier)
			};

            parameters[0].Value = bname;
            parameters[1].Value = author;
            parameters[2].Value = press;
            parameters[3].Value = price;
            parameters[4].Value = isbn;
            parameters[5].Value = pym;
            parameters[6].Value = cname;
            parameters[7].Value = descript;
            parameters[8].Value = pname;
            parameters[9].Value = new Guid(uid);

            try
            {
                //以下语句可以得到三个值，从而进行流程判断。一个是返回值，一个是影响的行数，一个是返回的指定字段值。
                ReturnValue=RunProcedure("Add_New_Book", parameters, out rowsAffected,out ReturnFieldValue);
            }
            catch (SqlException e)
            {
                throw new System.Exception(e.Message);
            }
            return (rowsAffected == 1);//只利用了这一个返回的值，ReturnValue暂时未用。
        }

        //修改一本书的信息
        public bool Modify_One_Book_Info
        (
            string bname,
            string author,
            string press,
            decimal price,
            string isbn,
            string pym,
            string cname,
            string descript,
            string pname,
            string uid,
            string bid,
            out int ReturnValue
            )
        {
            int rowsAffected;

            SqlParameter[]
                parameters = {
								 new SqlParameter("@bname",SqlDbType.VarChar,100), 
								 new SqlParameter("@author",SqlDbType.VarChar,100),
								 new SqlParameter("@press",SqlDbType.VarChar,100),
								 new SqlParameter("@price",SqlDbType.Decimal),
								 new SqlParameter("@isbn",SqlDbType.VarChar,13 ),
								 new SqlParameter("@pym",SqlDbType.VarChar,70 ),
								 new SqlParameter("@cname",SqlDbType.VarChar,1000),
								 new SqlParameter("@descript",SqlDbType.VarChar,4000 ),
								 new SqlParameter("@pname",SqlDbType.VarChar,500 ),
								 new SqlParameter("@uid",SqlDbType.UniqueIdentifier),
								 new SqlParameter("@bid",SqlDbType.VarChar,12)
			};

            parameters[0].Value = bname;
            parameters[1].Value = author;
            parameters[2].Value = press;
            parameters[3].Value = price;
            parameters[4].Value = isbn;
            parameters[5].Value = pym;
            parameters[6].Value = cname;
            parameters[7].Value = descript;
            parameters[8].Value = pname;
            parameters[9].Value = new Guid(uid);
            parameters[10].Value = bid;

            try
            {
                ReturnValue = RunProcedure("Modify_One_Book_Info", parameters, out rowsAffected);
            }
            catch (SqlException e)
            {
                throw new System.Exception(e.Message);
            }
            return (rowsAffected == 1);
        }




        //得到某位家庭的所有书籍。利用数据表集方式。
        public DataSet Get_One_Family_Book_From_UID(string UID, string TableName, int PageIndex, int PageSize, out int PageCount, out int TotalCount)
        {
            SqlParameter[]
                parameters = {
								 new SqlParameter("@UID", SqlDbType.UniqueIdentifier),
								 new SqlParameter("@pageindex", SqlDbType.Int),
								 new SqlParameter("@pagesize", SqlDbType.Int)
							 };

            parameters[0].Value = new Guid(UID);
            parameters[1].Value = PageIndex;
            parameters[2].Value = PageSize;

            using (DataSet ds = RunProcedure("Get_One_Family_Book_From_UID", parameters, TableName, out PageCount, out TotalCount))
            {
                return ds;
            }
        }

        //得到某位家庭的所有书籍。利用只读向前数据集方式。
        //public ArrayList Get_One_Family_Book_From_UID(string UID, int PageIndex, int PageSize)
        //{
        //    SqlParameter[]
        //        parameters = {
        //                         new SqlParameter("@UID", SqlDbType.UniqueIdentifier),
        //                         new SqlParameter("@pageindex", SqlDbType.Int),
        //                         new SqlParameter("@pagesize", SqlDbType.Int)
        //                     };

        //    parameters[0].Value = new Guid(UID);
        //    parameters[1].Value = PageIndex;
        //    parameters[2].Value = PageSize;

        //    SqlDataReader sdr = RunProcedure("Get_Page_DataSet_Book_From_UID", parameters);

        //    ArrayList array = new ArrayList();

        //    int i = 1;
        //    while (sdr.Read())
        //    {
        //        Hashtable record = new Hashtable();
        //        record["XID"] = i;                                //增加序号列
        //        for (int j = 0; j < sdr.FieldCount; j++)
        //        {
        //            object cellValue = sdr[j];
        //            if (cellValue.GetType() == typeof(DBNull))
        //            {
        //                cellValue = null;
        //            }
        //            record[sdr.GetName(j)] = cellValue;
        //        }
        //        array.Add(record);
        //        i++;
        //    }

        //    Connection.Close();//必须在这里关闭连接。经免连接过多，发生连接资源不足问题。前者对象来自父类。
        //    return array;
        //}


        //得到一本书的相关信息
        public DataSet Get_One_Book_Info_From_BID(string BID)
        {
            SqlParameter[]
                parameters = {
								 new SqlParameter("@BID", SqlDbType.VarChar,12),
							 };

            parameters[0].Value = BID;

            using (DataSet ds = RunProcedure("Get_One_Book_Info_From_BID", parameters, "OneBookInfo"))
            {
                return ds;
            }
        }



        //下架或上架一本书
        public bool Shelf_One_Book_From_BID
        (
            string uid,
            string bid,
            out int ReturnValue,
            out string ReturnFieldValue
            )
        {
            int rowsAffected;

            SqlParameter[]
                parameters = {
								 new SqlParameter("@uid",SqlDbType.UniqueIdentifier),
								 new SqlParameter("@bid",SqlDbType.VarChar,12)
			};

            parameters[0].Value = new Guid(uid);
            parameters[1].Value = bid;

            try
            {
                ReturnValue = RunProcedure("Shelf_One_Book_From_BID", parameters, out rowsAffected, out ReturnFieldValue);
            }
            catch (SqlException e)
            {
                throw new System.Exception(e.Message);
            }
            return (rowsAffected == 1);
        }

        //查询某个分类图书列表
        public DataSet Search_Book_List_From_CategoryID(string cname, string UID,string TableName, int PageIndex, int PageSize, out int PageCount, out int TotalCount)
        {
            SqlParameter[]
                parameters = {
								 new SqlParameter("@cname", SqlDbType.VarChar,200),
                                 new SqlParameter("@UID",SqlDbType.UniqueIdentifier),
								 new SqlParameter("@pageindex", SqlDbType.Int),
								 new SqlParameter("@pagesize", SqlDbType.Int)
							 };

            parameters[0].Value = cname;
            parameters[1].Value = new Guid(UID);
            parameters[2].Value = PageIndex;
            parameters[3].Value = PageSize;

            using (DataSet ds = RunProcedure("Search_Book_List_From_CategoryID", parameters, TableName, out PageCount, out TotalCount))
            {
                return ds;
            }
        }


        //查询图书关键字，获得列表。
        public DataSet Search_Book_List_From_BookName(string bname, string UID, string TableName, int PageIndex, int PageSize, out int PageCount, out int TotalCount)
        {
            SqlParameter[]
                parameters = {
								 new SqlParameter("@bname", SqlDbType.VarChar,100),
                                 new SqlParameter("@UID",SqlDbType.UniqueIdentifier),
								 new SqlParameter("@pageindex", SqlDbType.Int),
								 new SqlParameter("@pagesize", SqlDbType.Int)
							 };

            parameters[0].Value = bname;
            parameters[1].Value = new Guid(UID);
            parameters[2].Value = PageIndex;
            parameters[3].Value = PageSize;

            using (DataSet ds = RunProcedure("Search_Book_List_From_BookName", parameters, TableName, out PageCount, out TotalCount))
            {
                return ds;
            }
        }

        //得到某家庭下架书
        public DataSet Get_Off_Shelf_Books_From_UID(string UID, string TableName, int PageIndex, int PageSize, out int PageCount, out int TotalCount)
        {
            SqlParameter[]
                parameters = {
								 new SqlParameter("@UID", SqlDbType.UniqueIdentifier),
								 new SqlParameter("@pageindex", SqlDbType.Int),
								 new SqlParameter("@pagesize", SqlDbType.Int)
							 };

            parameters[0].Value = new Guid(UID);
            parameters[1].Value = PageIndex;
            parameters[2].Value = PageSize;

            using (DataSet ds = RunProcedure("Get_Off_Shelf_Books_From_UID", parameters, TableName, out PageCount, out TotalCount))
            {
                return ds;
            }
        }

        //得到某家庭上架书
        public DataSet Get_ON_Shelf_Books_From_UID(string UID, string TableName, int PageIndex, int PageSize, out int PageCount, out int TotalCount)
        {
            SqlParameter[]
                parameters = {
								 new SqlParameter("@UID", SqlDbType.UniqueIdentifier),
								 new SqlParameter("@pageindex", SqlDbType.Int),
								 new SqlParameter("@pagesize", SqlDbType.Int)
							 };

            parameters[0].Value = new Guid(UID);
            parameters[1].Value = PageIndex;
            parameters[2].Value = PageSize;

            using (DataSet ds = RunProcedure("Get_ON_Shelf_Books_From_UID", parameters, TableName, out PageCount, out TotalCount))
            {
                return ds;
            }
        }


    }
}
